﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;

namespace SBACN.WebSite.Common
{
    public class ExcelAccess
    {
        /// 从文档中获得数据
        /// </summary>
        /// <param name="xlsPath"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static DataSet GetExcelRows(string xlsPath, string sheetName)
        {
			string drv = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsPath + ";Extended Properties='Excel 8.0;HDR=Yes'";
            string strSQL = "SELECT * FROM [" + sheetName + "$]";
			OleDbConnection conn = new OleDbConnection(drv);
			conn.Open();
            DataSet ds = new DataSet();
            try
            {
                OleDbDataAdapter da = new OleDbDataAdapter(strSQL, drv);
                da.Fill(ds);
                da.Dispose();
            }
            catch (Exception ex)
            {
                //logImportInfo.FileName = xlsPath;
                //logImportInfo.SheetName = sheetName;
                //logImportInfo.CreateDate = DateTime.Now;
                //logImportInfo.Remark = "从Excel中获取数据出现异常";
                //logImportInfo.Extation1 = ex.ToString();
                //LogBLL.InsertImportLog(logImportInfo);
            }
            List<DataRow> listRow = new List<DataRow>();
            if (ds.Tables.Count > 0)
            {
                DataTable table = ds.Tables[0];
                foreach (DataRow row in table.Rows)
                {
                    if (!HaveData(row))
                    {
                        listRow.Add(row);
                    }
                }
                foreach (DataRow row in listRow)
                {
                    ds.Tables[0].Rows.Remove(row);
                }
            }
            //if (ds.Tables.Count > 0)
            //{
            //    logImportInfo.FileName = xlsPath;
            //    logImportInfo.SheetName = sheetName;
            //    logImportInfo.CreateDate = DateTime.Now;
            //    logImportInfo.Remark = ds.Tables[0].Rows.Count.ToString();
            //    LogBLL.InsertImportLog(logImportInfo);
            //}
            return ds;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        public static bool HaveData(DataRow dr)
        {
            bool result = false;
            for (int i = 0; i < dr.ItemArray.GetLength(0); i++)
            {
                if (!string.IsNullOrEmpty(dr.ItemArray[i].ToString()))
                {
                    return true;
                }
            }
            return result;
        }
        /// 导出到Excel
    }
}